---
sidebar_label: Create relationships
sidebar_position: 1
description: Create relationships between MS SQL Server tables/views in Hasura
keywords:
  - hasura
  - docs
  - ms sql server
  - schema
  - relationship
  - create
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Thumbnail from '@site/src/components/Thumbnail';
import GraphiQLIDE from '@site/src/components/GraphiQLIDE';

# MS SQL Server: Create Relationships

## Introduction

A relationship from one table/view to another can be created by defining a link between a column of the table/view to a
column of the other table/view.

Typically, relationships between tables are defined using foreign-key constraints. But in some cases, it might not be
possible to use foreign-key constraints to create the relationship. For example, while trying to create a relationship
involving a view or Native Query since foreign-keys cannot be created on them.

## Using foreign keys {#ms-sql-server-relationships-using-fkey}

Say we created two tables, `authors(id, name)` and `articles(id, title, content, rating, author_id)`.

Let us now connect these tables to enable nested queries using a foreign-key:

### Step 1: Add foreign-key constraint

Let's add a foreign-key constraint to the `author_id` column in the `articles` table.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

In the Console, navigate to the `Modify` tab of the `articles` table. Click the `Add` button in the Foreign Keys section
and configure the `author_id` column as a foreign-key for the `id` column in the `authors` table:

<Thumbnail src="/img/schema/add-foreign-key-mssql.png" alt="Add foreign-key constraint" width="700px" />

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
following SQL statement to the `up.sql` file:

```sql
ALTER TABLE articles
ADD FOREIGN KEY (author_id) REFERENCES authors(id);
```

Add the following statement to the `down.sql` file in case you need to
[roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the above statement:

```sql
ALTER TABLE articles
DROP CONSTRAINT articles_author_id_fkey;
```

Apply the migration by running:

```bash
hasura migrate apply
```

</TabItem>
<TabItem value="api" label="API">

You can add a foreign-key constraint using the
[schema_run_sql Metadata API](/api-reference/schema-api/run-sql.mdx#schema-run-sql) :

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db-name>",
    "sql": "ALTER TABLE articles ADD FOREIGN KEY (author_id) REFERENCES authors(id);"
  }
}
```

</TabItem>
</Tabs>

### Step 2: Create an object relationship

Each article has one author. This is an `object relationship`.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

The Console infers potential relationships using the foreign-key created above and recommends these in the
`Relationships` tab of the `articles` table.

Add an `object relationship` named `author` for the `articles` table as shown here:

<Thumbnail src="/img/schema/add-1-1-relationship.png" alt="Create an object relationship" width="1100px" />

</TabItem>
<TabItem value="cli" label="CLI">

You can add an object relationship in the `tables.yaml` file inside the `metadata` directory:

```yaml {4-7}
- table:
    schema: public
    name: articles
  object_relationships:
    - name: author
      using:
        foreign_key_constraint_on: author_id
- table:
    schema: public
    name: authors
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can create an object relationship by using the
[mssql_create_object_relationship](/api-reference/metadata-api/relationship.mdx#mssql-create-object-relationship)
Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "mssql_create_object_relationship",
  "args": {
    "source": "<db_name>",
    "table": "articles",
    "name": "author",
    "using": {
      "foreign_key_constraint_on" : ["author_id"]
    }
  }
}
```

</TabItem>
</Tabs>

We can now run a nested object query that is based on this `object relationship`.

Fetch a list of articles and each article's author:

<GraphiQLIDE
  query={`query {
  articles {
    id
    title
    author {
      id
      name
    }
  }
}`}
  response={`{
  "data": {
    "articles": [
      {
        "id": 1,
        "title": "sit amet",
        "author": {
          "name": "Anjela",
          "id": 4
        }
      },
      {
        "id": 2,
        "title": "a nibh",
        "author": {
          "name": "Beltran",
          "id": 2
        }
      },
      {
        "id": 3,
        "title": "amet justo morbi",
        "author": {
          "name": "Anjela",
          "id": 4
        }
      }
    ]
  }
}`}
/>

### Step 3: Create an array relationship

An author can write multiple articles. This is an `array relationship`.

You can add an `array relationship` in the same fashion as an `object relationship` as shown above.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

On the Console, add an `array relationship` named `articles` for the `authors` table as shown here:

<Thumbnail src="/img/schema/add-1-many-relationship.png" alt="Create an array relationship" width="1100px" />

We can now run a nested object query that is based on this `array relationship`.

</TabItem>
<TabItem value="cli" label="CLI">

You can add an array relationship in the `tables.yaml` file inside the `metadata` directory:

```yaml {11-18}
- table:
    schema: public
    name: articles
  object_relationships:
    - name: author
      using:
        foreign_key_constraint_on: author_id
- table:
    schema: public
    name: authors
  array_relationships:
    - name: articles
      using:
        foreign_key_constraint_on:
          column: author_id
          table:
            schema: public
            name: articles
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can create an array relationship by using the
[mssql_create_array_relationship](/api-reference/metadata-api/relationship.mdx#mssql-create-array-relationship) metadata
API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "mssql_create_array_relationship",
  "args": {
    "table": "author",
    "name": "articles",
    "source": "<db_name>",
    "using": {
      "foreign_key_constraint_on" : {
          "table" : "articles",
          "columns" : ["author_id"]
        }
      }
    }
  }
```

</TabItem>
</Tabs>

Fetch a list of authors and a nested list of each author's articles:

<GraphiQLIDE
  query={`query {
  authors {
    id
    name
    articles {
      id
      title
    }
  }
}`}
  response={`{
  "data": {
    "authors": [
      {
        "id": 1,
        "name": "Justin",
        "articles": [
          {
            "id": 15,
            "title": "vel dapibus at"
          },
          {
            "id": 16,
            "title": "sem duis aliquam"
          }
        ]
      },
      {
        "id": 2,
        "name": "Beltran",
        "articles": [
          {
            "id": 2,
            "title": "a nibh"
          },
          {
            "id": 9,
            "title": "sit amet"
          }
        ]
      },
      {
        "id": 3,
        "name": "Sidney",
        "articles": [
          {
            "id": 6,
            "title": "sapien ut"
          },
          {
            "id": 11,
            "title": "turpis eget"
          },
          {
            "id": 14,
            "title": "congue etiam justo"
          }
        ]
      }
    ]
  }
}`}
/>

## Using manual relationships {#ms-sql-server-create-manual-relationships}

Let's say you have a table `authors (id, name)` and a [view](/schema/ms-sql-server/views.mdx) ' +
'`author_avg_rating (id, avg)` which has the average rating of articles for each author.

Let us now create an `object relationship` called `avg_rating` from the `authors` table to the `author_avg_rating` view
using a manual relationship:

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

**Step 1: Open the manual relationship section**

- Open the Console and navigate to the `Data -> authors -> Relationships` tab.
- Click on the `Add Relationship` button:

<Thumbnail src="/img/schema/add-manual-rel-open-widget.png" alt="Open the manual relationship widget" width="1100px" />

**Step 2: Fill in the relationships details **

Once the widget is open, fill in the name of the relationship and pick a reference source

<Thumbnail src="/img/schema/add-manual-rel-add-name-pick-source.png" alt="Define the relationship name and target" width="700px" />

This will open up a "details" section below where you can fill in the rest of the relationship definition

<Thumbnail src="/img/schema/add-manual-rel-fill-details.png" alt="Fill the relationship details" width="700px" />


**Step 3: Create the relationship**

Now click on the `Create Relationship` button to proceed.

<Thumbnail src="/img/schema/add-manual-rel-create-final-step.png" alt="Fill the relationship details" width="700px" />

</TabItem>
<TabItem value="cli" label="CLI">

You can add a manual relationship in the `tables.yaml` file inside the `metadata` directory:

```yaml {7-15}
- table:
    schema: public
    name: articles
- table:
    schema: public
    name: authors
  object_relationships:
    - name: avg_rating
      using:
        manual_configuration:
          remote_table:
            schema: public
            name: author_average_rating
          column_mapping:
            id: author_id
- table:
    schema: public
    name: author_average_rating
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can add a manual relationship by using the
[mssql_create_object_relationship](/api-reference/metadata-api/relationship.mdx#mssql-create-object-relationship)
Metadata API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "mssql_create_object_relationship",
  "args": {
    "table": "authors",
    "name": "avg_rating",
    "source": "<db_name>",
    "using": {
      "manual_configuration": {
        "remote_table": "author_average_rating",
        "column_mapping": {
          "id": "author_id"
        }
      }
    }
  }
}
```

</TabItem>
</Tabs>

We can now run a nested object query that is based on this `object relationship`.

Fetch a list of authors with the average rating of their articles:

<GraphiQLIDE
  query={`query {
  authors {
    id
    name
    avg_rating {
      avg
    }
  }
}`}
  response={`{
  "data": {
    "authors": [
      {
        "id": 1,
        "name": "Justin",
        "avg_rating": {
          "avg": 2.5
        }
      },
      {
        "id": 2,
        "name": "Beltran",
        "avg_rating": {
          "avg": 3
        }
      },
      {
        "id": 3,
        "name": "Sidney",
        "avg_rating": {
          "avg": 2.6666666666666665
        }
      }
    ]
  }
}`}
/>

## Tracking existing relationships inferred via foreign-keys

As mentioned in the Introduction section above, relationships can be inferred via foreign-keys that exist in your
database:

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

The Console infers potential relationships using existing foreign-keys and recommends these on the `Data -> Schema` page

<Thumbnail src="/img/schema/schema-track-relationships.png" alt="Track all relationships" width="800px" />

You can choose to track the relationships individually using the `Track` buttons or hit the `Track all` button to track
all the inferred relationships in one go.

</TabItem>
<TabItem value="cli" label="CLI">

You can add relationships in the `tables.yaml` file inside the `metadata` directory:

```yaml {4-7,11-18}
- table:
    schema: public
    name: articles
  object_relationships:
    - name: author
      using:
        foreign_key_constraint_on: author_id
- table:
    schema: public
    name: authors
  array_relationships:
    - name: articles
      using:
        foreign_key_constraint_on:
          column: author_id
          table:
            schema: public
            name: articles
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can create multiple relationships by using the
[mssql_create_object_relationship](/api-reference/metadata-api/relationship.mdx#mssql-create-object-relationship) and
the [mssql_create_array_relationship](/api-reference/metadata-api/relationship.mdx#mssql-create-array-relationship)
Metadata APIs:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bulk",
  "args": [
    {
      "type": "mssql_create_object_relationship",
      "args": {
        "source": "<db_name>",
        "table": "articles",
        "name": "author",
        "using": {
          "foreign_key_constraint_on": "author_id"
        }
      }
    },
    {
      "type": "mssql_create_array_relationship",
      "args": {
        "source": "<db_name>",
        "table": "authors",
        "name": "articles",
        "using": {
          "foreign_key_constraint_on" : {
            "table" : "articles",
            "column" : "author_id"
          }
        }
      }
    }
  ]
}
```

</TabItem>
</Tabs>

## Tracking relationships between tables and Native Queries

As mentioned in the Introduction section above, a relationship from a table to a Native Query can only be set up manually.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="api" label="API">

Given a table named `articles` and an existing Native Query named `get_author`,
we can set up a relationship between the two.

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "bulk",
  "args": [
    {
      "type": "mssql_create_object_relationship",
      "args": {
        "source": "<db_name>",
        "table": "articles",
        "name": "author",
        "using": {
          "manual_configuration": {
            "remote_native_query": "get_author",
            "column_mapping": {
              "id": "author_id"
            }
          }
        }
      }
    }
  ]
}
```

</TabItem>
</Tabs>
